// 获取sequelize 实例
const { sequelize } = require("../model/connect");
// 传入数据模型函数,并的到 User 模型
const { Student } = require("../model/models")(sequelize);

const getAllStudents = async (ctx) => {
    try {
        const students = await Student.findAll();
        ctx.status = 200;
        ctx.body = {
            code: 10000,
            message: "查询成功",
            data: students,
        };
    } catch (error) {
        ctx.status = 500;
        ctx.body = {
            code: 10005,
            message: error.message,
        };
    }
};
const getStudentDetails = async (ctx) => {
    try {
        const { id } = ctx.params;
        const student = await Student.findByPk(id);
        if (!student) {
            ctx.status = 400;
            ctx.body = {
                code: 10004,
                message: "学生未找到",
            };
        }
        ctx.status = 200;
        ctx.body = {
            code: 10000,
            message: "查询成功",
            data: student,
        };
    } catch (error) {
        ctx.status = 500;
        ctx.body = {
            code: 10005,
            message: error.message,
        };
    }
};
const getStudentsByPage = async (ctx) => {
    try {
        const page = parseInt(ctx.query.page) || 1;
        const pageSize = parseInt(ctx.query.pageSize) || 10;
        // 后端查询
        // offset / limit
        // 跳过offset条取limit个
        const offset = (page - 1) * pageSize;
        const students = await Student.findAndCountAll({
            offset,
            limit: pageSize,
        });
        if (!students) {
            ctx.status = 400;
            ctx.body = {
                code: 10004,
                message: "查询失败",
            };
        }
        ctx.status = 200;
        ctx.body = {
            code: 10000,
            message: "查询成功",
            data: students,
        };
    } catch (error) {
        ctx.status = 500;
        ctx.body = {
            code: 10005,
            message: error.message,
        };
    }
};
const addStudent = async (ctx) => {
    try {
        const {
            name,
            age,
            gender,
            province,
            city,
            area,
            salary,
            hope_salary,
            classNumber,
        } = ctx.request.body;
        const newStudent = await Student.create({
            name,
            age,
            gender,
            province,
            city,
            area,
            salary,
            hope_salary,
            classNumber,
        });
        ctx.status = 200;
        ctx.body = {
            code: 10000,
            message: "创建成功",
            data: newStudent,
        };
    } catch (error) {
        ctx.status = 500;
        ctx.body = {
            code: 10005,
            message: error.message,
        };
    }
};
const deleteStudent = async (ctx) => {
    try {
        const { id } = ctx.params;
        const student = await Student.findByPk(id);
        if (!student) {
            ctx.status = 400;
            ctx.body = {
                code: 10004,
                message: "该数据不存在",
            };
            return;
        }
        await student.destroy();
        ctx.status = 204;
    } catch (error) {
        ctx.status = 500;
        ctx.body = {
            code: 10005,
            message: error.message,
        };
    }
};

const updateStudent = async (ctx) => {
    console.log(123);
    try {
        const { id } = ctx.params;
        const updateData = ctx.request.body;
        const student = await Student.findByPk(id);
        if (!student) {
            ctx.status = 400;
            ctx.body = {
                code: 10004,
                message: "该数据不存在",
            };
            return;
        }
        // 把新属性合并到原属性，覆盖原属性
        Object.assign(student, updateData);
        student.save();
        ctx.status = 200;
        ctx.body = {
            code: 10000,
            message: "修改学生成功",
            data: student,
        };
    } catch (error) {
        ctx.status = 500;
        ctx.body = {
            code: 10005,
            message: error.message,
        };
    }
};

// dql 查询语句  select x from x where x group by x having x order by x limit x
// avg count 。。。
// 聚合函数是分组之后调用的，但是分组之后，原数据一定丢失

const getDashboardData = async (ctx) => {
    try {
        // 页头聚合信息：就业平均薪资，就业班级人数，平均年龄，班级个数
        const overview = await Student.findOne({
            // 要拿那几列
            attributes: [
                // sequelize.fn 指的是调用函数，AVG函数被调用，作用域 salary列，最终聚合的结果叫做 salary
                [sequelize.fn("AVG", sequelize.col("salary")), "salary"],
                [sequelize.fn("AVG", sequelize.col("age")), "age"],
                [sequelize.fn("COUNT", sequelize.col("id")), "student_count"],
                // 先去重 后数数
                [
                    sequelize.fn(
                        "COUNT",
                        sequelize.fn("DISTINCT", sequelize.col("classNumber"))
                    ),
                    "class_count",
                ],
            ],
        });
        // 薪资趋势
        const salaryTrend = await Student.findAll({
            // 要拿那几列
            attributes: [
                [sequelize.fn("YEAR", sequelize.col("createdAt")), "year"],
                [sequelize.fn("MONTH", sequelize.col("createdAt")), "month"],
                [sequelize.fn("AVG", sequelize.col("salary")), "salary"],
            ],
            group: ["year", "month"],
        });

        const salaryDistrubution = await Student.findAll({
            attributes: [
                // 原始查询
                [
                    sequelize.literal(
                        `CASE
                            WHEN salary < 10000 THEN "一万以下"
                            WHEN salary BETWEEN 10000 AND 15000 THEN "1-1.5万"
                            WHEN salary BETWEEN 15000 AND 20000 THEN "1.5万-2万"
                            ELSE "2万以上"
                        END
                        `
                    ),
                    "salary_range",
                ],
                [sequelize.fn("COUNT", sequelize.col("id")), "student_count"],
            ],
            group: ["salary_range"],
        });
        const genderSalaryDistrubution = await Student.findAll({
            attributes: [
                // 原始查询
                [
                    sequelize.literal(
                        `CASE
                            WHEN salary < 10000 THEN "一万以下"
                            WHEN salary BETWEEN 10000 AND 15000 THEN "1-1.5万"
                            WHEN salary BETWEEN 15000 AND 20000 THEN "1.5万-2万"
                            ELSE "2万以上"
                        END
                        `
                    ),
                    "salary_range",
                ],
                "gender",
                [sequelize.fn("COUNT", sequelize.col("id")), "student_count"],
            ],
            group: ["salary_range", "gender"],
        });
        const provinceDistrubution = await Student.findAll({
            attributes: [
                "province",
                [sequelize.fn("COUNT", sequelize.col("id")), "value"],
            ],
            group: ["province"],
        });
        // 对于每个班级的每名同学的期望薪资和实际薪资，不能使用sql分组聚合，需要手动聚合
        const classSalary = await Student.findAll({
            attributes: ["classNumber", "name", "hope_salary", "salary"],
            order: [["classNumber", "ASC"]],
        });
        const classSalaryComparsion = {};
        classSalary.forEach((item) => {
            const { classNumber, name, hope_salary, salary } = item;
            // 如果没有这个班级，就将这个班级设置为空数组
            if (!classSalaryComparsion[classNumber]) {
                classSalaryComparsion[classNumber] = [];
            }
            classSalaryComparsion[classNumber].push({
                name,
                hope_salary,
                salary,
            });
        });
        ctx.status = 200;
        ctx.body = {
            overview,
            salaryTrend,
            salaryDistrubution,
            genderSalaryDistrubution,
            provinceDistrubution,
            classSalaryComparsion,
        };
    } catch (error) {
        ctx.status = 500;
        ctx.body = {
            code: 10005,
            message: error.message,
        };
    }
};

module.exports = {
    getAllStudents,
    getStudentDetails,
    getStudentsByPage,
    addStudent,
    deleteStudent,
    updateStudent,
    getDashboardData,
};
